Retail Stocktake Variance Model - Shrinkage at Cost & Retail, Tolerance Bands, ABC Analysis – Excel XLSX

The Retail Stocktake Variance Model tells a convenience, forecourt or grocery operator what walked, what it cost, and which lines are worth chasing. Most stocktake sheets report variance one way and quietly mislead; this one reports it both ways, because both are true and they answer different questions.

What it does: it values variance at COST (what hits your P&L and stock account) and at RETAIL (the revenue you'll never ring up), and shows the gap between them – the margin you lost. A tolerance band separates genuine count noise from real shrinkage, so not every line looks like theft. Shrinkage is expressed as a percentage of sales measured against sales since the last count (not annual sales – the single most common way this metric gets quoted wrong), and benchmarked against your target and an industry rate, with the annual prize from closing the gap. An ABC (Pareto) classification ranks lines by value so counting discipline goes where the money is, positive and negative variances are reported separately rather than netted, and recount flags mark the lines to re-check before you sign the count off.

Main features: variance at cost and retail with lost margin; a tolerance band; shrinkage as a % of sales, benchmarked; ABC analysis; gross-versus-net variance; recount flags; and eleven integrity checks including a scale sanity check. 340 live formulas, zero formula errors.

How to work with it: enter your annual and period sales, tolerance and targets, then your counted lines (book qty, counted qty, unit cost, unit retail) in the blue cells; everything else calculates. A worked example is preloaded.

Why you need it: it is honest – the model values the count but doesn't attribute cause; theft, wastage and admin error all land in the same variance. Fully unlocked, every formula visible. Currency-agnostic; treat $ as your currency. For analysis only.